﻿Imports System.Web.Script.Serialization
Imports System.Web.Script.Services
Public Class oDocLine
    Public Function UpdateJsonDocLine(ByVal lineNum As Integer, ByVal docEntry As Integer, ByVal itemID As String, ByVal itemCode As String, ByVal itemName As String, ByVal warehouseID As String, _
                                     ByVal quantity As Double, ByVal basePrice As Double, ByVal discountPercent As Integer, ByVal discountAmt As Double, _
                                     ByVal priceAfterDiscount As Double, ByVal totalAfterDiscount As Double, _
                                     ByVal databaseName As String, ByVal isInsert As Boolean) As String
        Dim connect As New Connection()
        Try
            Dim errMsg As String = connect.setSQLDB(databaseName)
            If errMsg.Length = 0 Then
                Dim params = New Object() {docEntry, itemID, itemCode, itemName, warehouseID, quantity, basePrice, discountPercent, discountAmt, priceAfterDiscount, totalAfterDiscount}
                Dim query As New StringBuilder
                If isInsert = True Then
                    query.Append("INSERT INTO [oDocLine] ")
                    query.Append("  ([DocEntry]")
                    query.Append("  ,[ItemID]")
                    query.Append("  ,[ItemCode]")
                    query.Append("  ,[ItemName]")
                    query.Append("  ,[WarehouseID]")
                    query.Append("  ,[Quantity]")
                    query.Append("  ,[BasePrice]")
                    query.Append("  ,[DiscountPercent]")
                    query.Append("  ,[DiscountAmount]")
                    query.Append("  ,[PriceAfterDiscount]")
                    query.Append("  ,[TotalAfterDiscount])")
                    query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11)")
                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), params)
                Else
                    query.Append("UPDATE  [oDocLine] SET ")
                    query.Append(" [ItemID] = @Param1")
                    query.Append(" ,[ItemCode] = @Param2")
                    query.Append(" ,[ItemName] = @Param3")
                    query.Append(" ,[WarehouseID] = @Param4")
                    query.Append(" ,[Quantity] = @Param5")
                    query.Append(" ,[BasePrice] = @Param6")
                    query.Append(" ,[DiscountPercent] = @Param7")
                    query.Append(" ,[DiscountAmount] = @Param8")
                    query.Append(" ,[PriceAfterDiscount] =@Param9")
                    query.Append(" ,[TotalAfterDiscount] = @Param10")
                    query.Append(" WHERE [DocEntry] = @Param11 AND LineNum = @Param12")
                    connect.Object_Execute_SAP(query.ToString(), New Object() {itemID, itemCode, itemName, warehouseID, quantity, basePrice, discountPercent, discountAmt, priceAfterDiscount, totalAfterDiscount, docEntry, lineNum})
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception

            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Public Function GetDocLineByDocEntry(ByVal dataBaseName As String, ByVal docEntry As String) As DataSet
        Dim connect As New Connection()
        Try
            Dim ds As New DataSet("oDocLine")
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            If errMsg.Length = 0 Then
                Dim query As New StringBuilder
                query.Append("SELECT T0.*,T1.DocStatus,T2.SalesPersonCode,T2.SalesPersonName,oWarehouse.WarehouseName,T3.ReferenceName")
                query.Append(" FROM oDocLine T0 JOIN oDocHeader T1 ON T0.DocEntry = T1.DocEntry")
                query.Append(" JOIN oWarehouse ON T0.WarehouseID = oWarehouse.WarehouseID")
                query.Append(" LEFT JOIN oSalesPerson T2 ON T2.SalesPersonID = T0.SalesPersonID")
                query.Append(" LEFT JOIN oReferenceValue T3 ON (T3.ReferenceValue = T0.ItemType AND T3.ReferenceCode ='ItemType')")
                query.Append("  WHERE T0.DocEntry = @Param1")
                ds = connect.ObjectGetAll_Query_SAP(query.ToString(), New Object() {docEntry})
            End If
            Return ds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function CreateMarketingDocument(ByVal dataBaseName As String, ByVal docEntry As String, ByVal isUpdate As Boolean, ByVal lineJson As String) As String
        Dim connect As New Connection()
        Try
            Dim jss As New JavaScriptSerializer()
            Dim lstLine As List(Of oLine) = jss.Deserialize(Of List(Of oLine))(lineJson)
            Dim errMsg As String = connect.setSQLDB(dataBaseName)
            Dim query As New StringBuilder
            If errMsg.Length = 0 AndAlso lstLine IsNot Nothing Then
               
                If isUpdate = False Then
                    For Each line In lstLine
                        'Get item Infor
                        Dim item As New oItem
                        Dim itemCode As String = String.Empty
                        Dim ds As DataSet = item.GetItemByID(dataBaseName, line.ItemID)
                        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0 Then
                            line.ItemCode = ds.Tables(0).Rows(0)("ItemCode")
                        End If
                    Next
                    query.Append("INSERT INTO [oDocLine] ")
                    query.Append(" ([DocEntry]")
                    query.Append(" ,[ItemID]")
                    query.Append(" ,[ItemCode]")
                    query.Append(" ,[ItemName]")
                    query.Append(" ,[WarehouseID]")
                    query.Append(" ,[Quantity]")
                    query.Append(" ,[BasePrice]")
                    query.Append(" ,[LineTotal]")
                    query.Append(" ,[SalesPersonID]")
                    query.Append(" ,[ItemType]")
                    query.Append(" ,[DiscountAmount]")
                    query.Append(" ,[TotalAfterDiscount])")
                    query.Append(" VALUES (@DocEntry{0},@ItemID{0},@ItemCode{0},@ItemName{0},@WarehouseID{0},@Quantity{0}, ")
                    query.Append("@BasePrice{0},@LineTotal{0},@SalesPersonID{0},@ItemType{0},@DiscountAmount{0},@TotalAfterDiscount{0})")

                    Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), docEntry, lstLine)
                Else
                    Dim params = Nothing
                    For Each line In lstLine
                        Dim item As New oItem
                        Dim itemCode As String = String.Empty
                        Dim ds As DataSet = item.GetItemByID(dataBaseName, line.ItemID)
                        If ds IsNot Nothing AndAlso ds.Tables.Count > 0 AndAlso ds.Tables(0).Rows.Count > 0 Then
                            line.ItemCode = ds.Tables(0).Rows(0)("ItemCode")
                        End If
                        If line.LineStatus = "1" Then
                            query.Append("INSERT INTO [oDocLine] ")
                            query.Append(" ([DocEntry]")
                            query.Append(" ,[ItemID]")
                            query.Append(" ,[ItemCode]")
                            query.Append(" ,[ItemName]")
                            query.Append(" ,[WarehouseID]")
                            query.Append(" ,[Quantity]")
                            query.Append(" ,[BasePrice]")
                            query.Append(" ,[LineTotal]")
                            query.Append(" ,[SalesPersonID]")
                            query.Append(" ,[ItemType]")
                            query.Append(" ,[DiscountAmount]")
                            query.Append(" ,[TotalAfterDiscount])")
                            query.Append(" VALUES (@Param1,@Param2,@Param3,@Param4,@Param5,@Param6,@Param7,@Param8,@Param9,@Param10,@Param11,@Param12)")

                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {docEntry, line.ItemID, line.ItemCode, line.ItemName, line.WarehouseID, _
                                                                                                  line.Quantity, line.BasePrice, line.LineTotal, line.SalesPersonID, line.ItemType, _
                                                                                                   line.DiscountAmount, line.TotalAfterDiscount})
                        ElseIf line.LineStatus = "2" Then

                            query.Append("IF EXISTS (SELECT 1 FROM oDocHeader WHERE [DocEntry] = @Param12 AND (DocStatus IS NULL OR DocStatus <> 2) )")
                            query.Append("  UPDATE [oDocLine] SET ")
                            query.Append("  [ItemID] = @Param1")
                            query.Append(" ,[ItemCode] = @Param2")
                            query.Append(" ,[ItemName] = @Param3")
                            query.Append(" ,[WarehouseID] = @Param4")
                            query.Append(" ,[Quantity] = @Param5")
                            query.Append(" ,[BasePrice] = @Param6")
                            query.Append(" ,[LineTotal] =@Param7")
                            query.Append(" ,[SalesPersonID] = @Param8")
                            query.Append(" ,[ItemType] = @Param9")
                            query.Append(" ,[DiscountAmount] = @Param10")
                            query.Append(" ,[TotalAfterDiscount] = @Param11")
                            query.Append(" WHERE [DocEntry] = @Param12 AND [LineNum] = @Param13")

                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {line.ItemID, line.ItemCode, line.ItemName, line.WarehouseID, _
                                                                                                 line.Quantity, line.BasePrice, line.LineTotal, line.SalesPersonID, _
                                                                                                   line.ItemType, line.DiscountAmount, line.TotalAfterDiscount, docEntry, line.LineNum})
                        ElseIf line.LineStatus = "3" Then
                            query.Append("IF EXISTS (SELECT 1 FROM oDocHeader WHERE [DocEntry] = @Param1 AND (DocStatus IS NULL OR DocStatus <> 2) )")
                            query.Append("  DELETE FROM [oDocLine]")
                            query.Append("  WHERE [DocEntry] = @Param1 AND LineNum = @Param2")
                            Dim count As Integer = connect.Object_Execute_SAP(query.ToString(), New Object() {docEntry, line.LineNum})
                        End If

                    Next
                End If
            Else
                Return errMsg
            End If
        Catch ex As Exception
            Return ex.Message
        End Try
        Return String.Empty
    End Function
    Class oLine
        Property DocEntry As String
        Property ItemID As String
        Property ItemCode As String
        Property ItemName As String
        Property WarehouseID As String
        Property WarehouseName As String
        Property Quantity As Double
        Property BasePrice As Double
        Property DiscountPercent As Double
        Property DiscountAmount As Double
        Property PriceAfterDiscount As Double
        Property TotalAfterDiscount As Double
        Property LineStatus As String
        Property LineNum As String
        Property LineTotal As String
        Property SalesPersonID As String
        Property ItemType As String
        Public Sub New()
        End Sub
    End Class
End Class
